
package com.be.beadmin.product.repository;

import com.be.beadmin.common.entity.PageEntity;
import com.be.beadmin.product.dto.output.WorkOrderInforEx;
import com.be.beadmin.product.entity.WorkOrderInfor;
import org.springframework.data.domain.Page;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.CriteriaBuilder;
import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
* @author zjm
* @date 2023-03-30
**/
public interface WorkOrderInforRepository extends JpaRepository<WorkOrderInfor, String>, JpaSpecificationExecutor<WorkOrderInfor> {

    WorkOrderInfor findWorkOrderInforByIdAndDelete(String id, Integer delete);

    WorkOrderInfor findWorkOrderInforById(String id);

    List<WorkOrderInfor> findWorkOrderInforByBatchPlanIdAndDelete(String batchPlanId, Integer delete);

    WorkOrderInfor findWorkOrderInforByBatchProductIdAndDelete(String id, int delete);

    @Query(value = "select woi.wo from t_om_work_order_infor woi where woi.batch_product_id = ?1 and woi.is_delete = ?2",nativeQuery = true)
    String findWoByBatchProductIdAndDelete(String id, int delete);
    WorkOrderInfor findWorkOrderInforsByReleaseFlagAndDelete(String id, int delete);

    List<WorkOrderInfor> findWorkOrderInforsByBatchProductIdInAndDelete(List<String> batchProductIds, Integer delete);

    List<WorkOrderInfor> findWorkOrderInforsByBatchProductIdInAndScheduledFlagAndDelete(List<String> batchProductIds, Integer scheduledFlag, Integer delete);


    List<WorkOrderInfor> findWorkOrderInforsByBatchProductIdInAndScheduledFlagNotAndDelete(List<String> batchProductIds, Integer scheduledFlag, Integer delete);

    List<WorkOrderInfor> findWorkOrderInforsByReleaseFlagAndDelete(int releaseFlag,int delete);

    @Query(value = "select woi.batch_product_id from t_om_work_order_infor woi where woi.release_flag != ?1 and woi.is_delete = ?2",nativeQuery = true)
    List<String> findbatchProductByReleaseFlagAndDelete(int releaseFlag,int delete);

    @Query(value = "select woi.batch_product_id from t_om_work_order_infor woi where woi.release_flag != ?1 and woi.info_id in ?2 and woi.is_delete = ?3",nativeQuery = true)
    List<String> findbatchProductByReleaseFlagAndInfoIdInAndDelete(int releaseFlag,List<String> infoIds, int delete);

    @Query(value = "select woi.batch_product_id from t_om_work_order_infor woi where woi.id = ?1 and woi.is_delete = ?2",nativeQuery = true)
    String findBatchProductIdByWorkOrderInforIdAndDelete(String id,int delete);


    @Query(value = "select woi.batch_product_id from t_om_work_order_infor woi where woi.id in ?1 and woi.is_delete = ?2",nativeQuery = true)
    List<String> findBatchProductIdsByWorkOrderInforIdsAndDelete(List<String> woIds,int delete);
    WorkOrderInfor findWorkOrderInforByInfoId(String infoId);


    List<WorkOrderInfor> findWorkOrderInfosrByInfoIdAndDelete(String infoId,int delete);

    @Query(value = "select woi.wo from t_om_work_order_infor woi where woi.info_id = ?1 and woi.state != ?2 and woi.is_delete = ?3",nativeQuery = true)
    List<String> findWoByInfoIdAndNotStateAndDelete(String infoId,int state, int delete);

    List<WorkOrderInfor> findWorkOrderInforsByDelete(int delete);



    WorkOrderInfor findWorkOrderInforByWo(String wo);
    WorkOrderInfor findWorkOrderInforByWoAndDelete(String wo,int delete);

    Integer countWorkOrderInforByDelete(int delete);

    @Query(value = "select count(*) from t_om_work_order_infor woi where woi.state != 0",nativeQuery = true)
    Integer findStartedWorkOrderInforNumber();

    Integer countWorkOrderInforByStateAndDelete(int state, int delete);

    Integer countWorkOrderInforByAlertStatusAndDelete(int alertStatus, int delete);

    @Query(value = "select count(*) from WorkOrderInfor wo where (month(wo.closeTime) = ?1 or ?1 is NULL) and year(wo.closeTime) = ?2 and wo.delete = 0")
    Integer countYearMonthWorkOrderInforByStateAndCloseTimeAndDelete(Integer month, Integer year);

    @Modifying
    @Transactional
    @Query(value = "update t_om_work_order_infor woi set woi.is_delete = ?1 where woi.info_id = ?2",nativeQuery = true)
    int deleteWorkOrderInforByInfo(int delete , String infoId);

    @Modifying
    @Transactional
    @Query(value = "update WorkOrderInfor wo set wo.batchCount = (select max(bp.batchCount) from BatchPlan bp where bp.id = ?1 and bp.delete = 0) where wo.infoId in (select bp.infoId from BatchPlan bp where bp.id = ?1 and bp.delete = 0)  ")
    void updateBatchCountByBatchPlan(String id);

    @Modifying
    @Transactional
    @Query(value = "update WorkOrderInfor wo set wo.batchCount = ?1 where wo.infoId = ?2")
    void updateBatchCountByInfoId(int planCount, String infoId);

    @Query(value = "select woi.wo from t_om_work_order_infor woi where woi.is_delete = 0 and woi.wo like CONCAT('%',?1,'%')",nativeQuery = true)
    List<String> findWosBySpecifiedCondition(String s);

    List<WorkOrderInfor> findWorkOrderInforsByInfoIdAndDelete(String infoId, int i);

    List<WorkOrderInfor> findWorkOrderInforsByProjectCodeAndDelete(String projectCode , Integer delete);


    @Query(value = "SELECT ps.process_name from (SELECT p.id, pmi.process_name, p.show_self_inspection from t_om_product_manufact_infor pmi LEFT JOIN t_dm_process p  on pmi.process_id = p.id WHERE pmi.work_order_infor_code = ?1 and p.show_self_inspection = 1 and pmi.is_delete = 0 and p.is_delete = 0) ps LEFT JOIN t_qm_inspection_template qit on ps.process_name = qit.process_name and qit.product_type = ?2 and qit.type = '过程检验' and qit.state = 1 and qit.is_delete = 0 WHERE qit.id is NULL",nativeQuery = true)
    List<String> findNoSelfCheckTempleteProcess(String wo,String productType);

    List<WorkOrderInfor> findWorkOrderInforsByAlertStatusAndDelete(int alertStatus,int delete);
}